create or replace package body plobj_api
as

       type num_arr is table of number index by pls_integer;
       type vc_arr is table of varchar2(4000) index by pls_integer;
       type date_arr is table of date index by pls_integer;
       type clob_arr is table of clob index by pls_integer;
       type blob_arr is table of blob index by pls_integer;

       type value_map is table of value_pointer index by varchar2(30);
       type object_arr is table of value_map index by pls_integer;
       
       v_object_universe object_arr;
       
       number_type       constant pls_integer:= 1;
       date_type         constant pls_integer:= 2;
       varchar2_type     constant pls_integer:= 3;
       clob_type         constant pls_integer:= 4;
       blob_type         constant pls_integer:= 5;
       
       v_num_values      num_arr; -- 1
       v_date_values     date_arr; -- 2
       v_vc_values       vc_arr; -- 3
       v_clob_values     clob_arr; -- 4
       v_blob_values     blob_arr; -- 5
       
       c_empty_value_map  value_map;  

       function split(p_buffer varchar2, p_sep varchar2)
       return vc_arr
       is
		idx      pls_integer;
		list     varchar2(32767) := p_buffer;
		splits   vc_arr;
		cnt      pls_integer:=1;
       begin

		if p_buffer is null then
		  return splits;
		end if;

		loop
		    idx := instr(list,p_sep);
		    if idx > 0 then
			splits(cnt):= substr(list,1,idx-1);
			cnt:=cnt+1;
			list := substr(list,idx+length(p_sep));
		    else
			splits(cnt):= list;
			exit;
		    end if;
		end loop;
		return splits;
       end split;

       function new_object
       return pls_integer
       as
         v_object_handle   pls_integer:= nvl(v_object_universe.last,0)+1;
       begin
         v_object_universe(v_object_handle):= c_empty_value_map;
         return v_object_handle;
       end;
       
       function get_object(p_object_handle pls_integer)
       return value_map
       as
       begin 
         return v_object_universe(p_object_handle);
       exception
        when no_data_found then
          raise_application_error(-20001,'Unknown object: '||p_object_handle);
       end;
       
       function new_value_pointer(p_handle pls_integer, p_type pls_integer)
       return value_pointer
       as
         v_value_pointer value_pointer;
       begin
         v_value_pointer.handle:= p_handle;
         v_value_pointer.type:= p_type;
         return v_value_pointer;
       end;
       
       procedure free_object(p_object_handle pls_integer)
       as
         v_field_name   varchar2(30);
         v_object       value_map:= get_object(p_object_handle);
         v_value        value_pointer;
       begin
       
         v_field_name:= v_object.first;
         
         while v_field_name is not null loop
         
            v_value:= v_object(v_field_name);
            
            if v_value.type = number_type then
              v_num_values.delete(v_value.handle);
            elsif v_value.type = date_type then
              v_date_values.delete(v_value.handle);
            elsif v_value.type = varchar2_type then
              v_vc_values.delete(v_value.handle);
            elsif v_value.type = clob_type then
              v_clob_values.delete(v_value.handle);
            elsif v_value.type = blob_type then
              v_blob_values.delete(v_value.handle);
            end if;
            
            v_field_name:= v_object.next(v_field_name);
            
         end loop;
         
         v_object_universe.delete(p_object_handle);
         
       end;
       
       function get_value_pointer (p_object_handle in pls_integer,
                                   p_field_name in varchar2)
       return value_pointer
       as
           v_object       value_map:= get_object(p_object_handle);     
       begin
         return v_object(lower(p_field_name));
       exception when no_data_found then
         --dbms_output.put_line('[get_value_pointer] no data found');
         --dump_object(p_object_handle);
         raise_application_error(-20001,'Unknown field '||p_field_name||' on object #'||p_object_handle);
       end;

       procedure copy (p_object_handle_from pls_integer,
                       p_object_handle_to   pls_integer)
       as
           v_object_from       value_map:= get_object(p_object_handle_from);     
           v_object_to         value_map:= get_object(p_object_handle_to);     
           v_field_name        varchar2(30);
           v_value             value_pointer;
       begin
         v_field_name:= v_object_from.first;
         
         while v_field_name is not null loop
         
            copy_field(p_object_handle_from, p_object_handle_to, v_field_name);
            
            v_field_name:= v_object_from.next(v_field_name);
            
         end loop;
       end copy;

       
       procedure set_field(p_object_handle pls_integer,
                           p_field_name    varchar2,
                           p_field_value   date)
       as
           v_object         value_map:= get_object(p_object_handle);     
           v_value_handle   pls_integer:= nvl(v_date_values.last,0)+1;
       begin
           v_object(lower(p_field_name)):= new_value_pointer(v_value_handle,date_type);
           v_date_values(v_value_handle):= p_field_value;
           v_object_universe(p_object_handle):= v_object;
       end;

       procedure set_field(p_object_handle pls_integer,
                           p_field_name    varchar2,
                           p_field_value   number)
       as
           v_object         value_map:= get_object(p_object_handle);     
           v_value_handle   pls_integer:= nvl(v_num_values.last,0)+1;
       begin
           v_object(lower(p_field_name)):= new_value_pointer(v_value_handle,number_type);
           v_num_values(v_value_handle):= p_field_value;
           v_object_universe(p_object_handle):= v_object;
       end;

       procedure set_field(p_object_handle pls_integer,
                           p_field_name    varchar2,
                           p_field_value   varchar2)
       as
           v_object         value_map:= get_object(p_object_handle);     
           v_value_handle   pls_integer:= nvl(v_vc_values.last,0)+1;
       begin
           v_object(lower(p_field_name)):= new_value_pointer(v_value_handle,varchar2_type);
           v_vc_values(v_value_handle):= p_field_value;
           v_object_universe(p_object_handle):= v_object;
       end;

       procedure set_field(p_object_handle pls_integer,
                           p_field_name    varchar2,
                           p_field_value   clob)
       as
           v_object         value_map:= get_object(p_object_handle);     
           v_value_handle   pls_integer:= nvl(v_clob_values.last,0)+1;
       begin
           v_object(lower(p_field_name)):= new_value_pointer(v_value_handle,clob_type);
           v_clob_values(v_value_handle):= p_field_value;
           v_object_universe(p_object_handle):= v_object;
       end;
                                  
       procedure set_field(p_object_handle pls_integer,
                           p_field_name    varchar2,
                           p_field_value   blob)
       as
           v_object         value_map:= get_object(p_object_handle);     
           v_value_handle   pls_integer:= nvl(v_blob_values.last,0)+1;
       begin
           v_object(lower(p_field_name)):= new_value_pointer(v_value_handle,blob_type);
           v_blob_values(v_value_handle):= p_field_value;
           v_object_universe(p_object_handle):= v_object;
       end;

       procedure get_field(p_object_handle pls_integer,
                           p_field_name    varchar2,
                           p_field_value   out nocopy date)
       as
           v_object        value_map:= get_object(p_object_handle);     
       begin
           --p_field_value:= v_date_values(v_object(p_field_name).handle);
           p_field_value:= get_date_value(v_object(lower(p_field_name)).handle,
                                          v_object(lower(p_field_name)).type);
       exception
        when no_data_found then
           p_field_value:= null;
       end;

       procedure get_field(p_object_handle pls_integer,
                           p_field_name    varchar2,
                           p_field_value   out nocopy number)
       as
           v_object        value_map:= get_object(p_object_handle);     
       begin
           --p_field_value:= v_num_values(v_object(p_field_name).handle);
           p_field_value:= get_number_value(v_object(lower(p_field_name)).handle,
                                            v_object(lower(p_field_name)).type);
       exception
        when no_data_found then
           p_field_value:= null;
       end;

       procedure get_field(p_object_handle pls_integer,
                           p_field_name    varchar2,
                           p_field_value   out nocopy varchar2)
       as
           v_object        value_map:= get_object(p_object_handle);     
       begin
           --p_field_value:= v_vc_values(v_object(p_field_name).handle);
           p_field_value:= get_varchar2_value(v_object(lower(p_field_name)).handle,
                                              v_object(lower(p_field_name)).type);
       exception
        when no_data_found then
           p_field_value:= null;
       end;

       procedure get_field(p_object_handle pls_integer,
                           p_field_name    varchar2,
                           p_field_value   out nocopy clob)
       as
           v_object        value_map:= get_object(p_object_handle);     
       begin
           --p_field_value:= v_clob_values(v_object(p_field_name).handle);
           p_field_value:= get_clob_value(v_object(lower(p_field_name)).handle,
                                          v_object(lower(p_field_name)).type);
       exception
        when no_data_found then
           p_field_value:= null;
       end;
                                  
       procedure get_field(p_object_handle pls_integer,
                           p_field_name    varchar2,
                           p_field_value   out nocopy blob)
       as
           v_object        value_map:= get_object(p_object_handle);     
       begin
           --p_field_value:= v_blob_values(v_object(p_field_name).handle);
           p_field_value:= get_blob_value(v_object(lower(p_field_name)).handle,
                                          v_object(lower(p_field_name)).type);
       exception
        when no_data_found then
           p_field_value:= null;
       end;
       
       /*function get_field_type(p_object_handle pls_integer,
                               p_field_name    varchar2)
       return pls_integer
       as
           v_object        value_map:= get_object(p_object_handle);     
       begin
         return v_object(p_field_name).type;
       end;*/

       function get_date_value(p_value_handle pls_integer,
                               p_value_type    varchar2)
       return date
       as
       begin
          if p_value_type = number_type then
            raise value_error;
          elsif p_value_type = date_type then
            return v_date_values(p_value_handle);
          elsif p_value_type = varchar2_type then
            return to_date(v_vc_values(p_value_handle));
          elsif p_value_type = clob_type then
            raise value_error;
          elsif p_value_type = blob_type then
            raise value_error;
          end if;
       end;

       function get_number_value(p_value_handle pls_integer,
                                 p_value_type    varchar2)
       return number
       as
       begin
          if p_value_type = number_type then
            return v_num_values(p_value_handle);
          elsif p_value_type = date_type then
            raise value_error;
          elsif p_value_type = varchar2_type then
            return to_number(v_vc_values(p_value_handle));
          elsif p_value_type = clob_type then
            raise value_error;
          elsif p_value_type = blob_type then
            raise value_error;
          end if;
       end;

       function get_varchar2_value(p_value_handle pls_integer,
                                   p_value_type    varchar2)
       return varchar2
       as
       begin
          if p_value_type = number_type then
            return to_char(v_num_values(p_value_handle));
          elsif p_value_type = date_type then
            return to_char(v_date_values(p_value_handle));
          elsif p_value_type = varchar2_type then
            return v_vc_values(p_value_handle);
          elsif p_value_type = clob_type then
            raise value_error;
          elsif p_value_type = blob_type then
            raise value_error;
          end if;
       end;

       function get_clob_value(p_value_handle pls_integer,
                               p_value_type    varchar2)
       return clob
       as
       begin
          if p_value_type = number_type then
            return to_char(v_num_values(p_value_handle));
          elsif p_value_type = date_type then
            return to_char(v_date_values(p_value_handle));
          elsif p_value_type = varchar2_type then
            return v_vc_values(p_value_handle);
          elsif p_value_type = clob_type then
            return v_clob_values(p_value_handle);
          elsif p_value_type = blob_type then
            raise value_error;
          end if;
       end;

       function get_blob_value(p_value_handle pls_integer,
                               p_value_type    varchar2)
       return blob
       as
       begin
          if p_value_type = number_type then
            raise value_error;
          elsif p_value_type = date_type then
            raise value_error;
          elsif p_value_type = varchar2_type then
            raise value_error;
          elsif p_value_type = clob_type then
            raise value_error;
          elsif p_value_type = blob_type then
            return v_blob_values(p_value_handle);
          end if;
       end;

       function is_field_null(p_object_handle pls_integer,
                              p_field_name    varchar2)
       return boolean
       as
           v_object        value_map:= get_object(p_object_handle);     
       begin
          if v_object(lower(p_field_name)).type = number_type then
            return get_number_value(v_object(p_field_name).handle,v_object(lower(p_field_name)).type) is null;
            --return v_num_values(v_object(p_field_name).handle) is null;
          elsif v_object(lower(p_field_name)).type = date_type then
            return get_date_value(v_object(p_field_name).handle,v_object(lower(p_field_name)).type) is null;
            --return v_date_values(v_object(p_field_name).handle) is null;
          elsif v_object(lower(p_field_name)).type = varchar2_type then
            return get_varchar2_value(v_object(p_field_name).handle,v_object(lower(p_field_name)).type) is null;
            --return v_vc_values(v_object(p_field_name).handle) is null;
          elsif v_object(lower(p_field_name)).type = clob_type then
            return get_clob_value(v_object(p_field_name).handle,v_object(lower(p_field_name)).type) is null;
            --return v_clob_values(v_object(p_field_name).handle) is null;
          elsif v_object(lower(p_field_name)).type = blob_type then
            return get_blob_value(v_object(p_field_name).handle,v_object(lower(p_field_name)).type) is null;
            --return v_blob_values(v_object(p_field_name).handle) is null;
          end if;
       exception when no_data_found then
         return true;
       end;

       procedure dump_object(p_object_handle pls_integer)
       as
         v_field_name     varchar2(30);
         v_object         value_map:= get_object(p_object_handle);
         v_value          value_pointer;
         v_null_replacer  varchar2(10):= '(null)';
       begin
       
         v_field_name:= v_object.first;
         
         dbms_output.put_line('Record #'||p_object_handle);
         dbms_output.put_line('[');
         
         while v_field_name is not null loop
         
            v_value:= v_object(v_field_name);
            
            if v_value.type = number_type then
              dbms_output.put_line('    '||v_field_name||': '||nvl(to_char(v_num_values(v_value.handle)),v_null_replacer));
            elsif v_value.type = date_type then
              dbms_output.put_line('    '||v_field_name||': '||nvl(to_char(v_date_values(v_value.handle)),v_null_replacer));
            elsif v_value.type = varchar2_type then
              dbms_output.put_line('    '||v_field_name||replace(': '''||nvl(v_vc_values(v_value.handle),v_null_replacer)||'''','''',''));
            elsif v_value.type = clob_type then
              dbms_output.put_line('    '||v_field_name||replace(': '''||nvl(dbms_lob.substr(v_clob_values(v_value.handle),1,50)||'''... (length: '||dbms_lob.getlength(v_clob_values(v_value.handle))||')',v_null_replacer),'''',''));
            elsif v_value.type = blob_type then
              if v_blob_values(v_value.handle) is null then
                 dbms_output.put_line('    '||v_field_name||': '||v_null_replacer);
              else
                 dbms_output.put_line('    '||v_field_name||': (blob) (length: '||dbms_lob.getlength(v_blob_values(v_value.handle))||')');
              end if;
            end if;
            
            v_field_name:= v_object.next(v_field_name);
            
         end loop;
          
         dbms_output.put_line(']');
         
       end;
       
       function to_clob(p_object_handle pls_integer)
       return clob
       as
         v_field_name     varchar2(30);
         v_object         value_map:= get_object(p_object_handle);
         v_value          value_pointer;
         v_null_replacer  varchar2(10):= '(null)';
         v_clob           clob;
         procedure put_line(p_buffer varchar2)
         as
         begin
            dbms_lob.writeappend(v_clob, length(p_buffer||chr(10)), p_buffer||chr(10));
         end;
       begin
       
         dbms_lob.createtemporary(v_clob,true,dbms_lob.call);

         v_field_name:= v_object.first;
         
         
         put_line('Record #'||p_object_handle);
         put_line('[');
         
         while v_field_name is not null loop
         
            v_value:= v_object(v_field_name);
            
            if v_value.type = number_type then
              put_line('    '||v_field_name||': '||nvl(to_char(v_num_values(v_value.handle)),v_null_replacer));
            elsif v_value.type = date_type then
              put_line('    '||v_field_name||': '||nvl(to_char(v_date_values(v_value.handle)),v_null_replacer));
            elsif v_value.type = varchar2_type then
              put_line('    '||v_field_name||replace(': '''||nvl(v_vc_values(v_value.handle),v_null_replacer)||'''','''',''));
            elsif v_value.type = clob_type then
              put_line('    '||v_field_name||replace(': '''||nvl(dbms_lob.substr(v_clob_values(v_value.handle),1,50)||'''... (length: '||dbms_lob.getlength(v_clob_values(v_value.handle))||')',v_null_replacer),'''',''));
            elsif v_value.type = blob_type then
              if v_blob_values(v_value.handle) is null then
                 put_line('    '||v_field_name||': '||v_null_replacer);
              else
                 put_line('    '||v_field_name||': (blob) (length: '||dbms_lob.getlength(v_blob_values(v_value.handle))||')');
              end if;
            end if;
            
            v_field_name:= v_object.next(v_field_name);
            
         end loop;
          
         put_line(']');
         
         return v_clob;
         
       end;

       procedure remove_field(p_object_handle pls_integer, 
                              p_field_name      varchar2)
       as
          v_object            value_map:= get_object(p_object_handle);
       begin
          v_object.delete(lower(p_field_name));
          v_object_universe(p_object_handle):= v_object;
       end;
       
       procedure rename_field(p_object_handle pls_integer, 
                              p_old_name      varchar2, 
                              p_new_name      varchar2)
       as
          v_object            value_map:= get_object(p_object_handle);
       begin
          v_object(lower(p_new_name)):= v_object(lower(p_old_name));
          v_object.delete(lower(p_old_name));
          v_object_universe(p_object_handle):= v_object;
       end;
       
       procedure copy_field(p_object_handle pls_integer, 
                            p_name          varchar2, 
                            p_new_name      varchar2)
       as
          v_value             value_pointer;
       begin
            v_value := get_value_pointer(p_object_handle, lower(p_name));
            
            if v_value.type = number_type then
              set_field(p_object_handle,lower(p_new_name),get_number_field(p_object_handle,lower(p_name)));
            elsif v_value.type = date_type then
              set_field(p_object_handle,lower(p_new_name),get_date_field(p_object_handle,lower(p_name)));
            elsif v_value.type = varchar2_type then
              set_field(p_object_handle,lower(p_new_name),get_varchar2_field(p_object_handle,lower(p_name)));
            elsif v_value.type = clob_type then
              set_field(p_object_handle,lower(p_new_name),get_clob_field(p_object_handle,lower(p_name)));
            elsif v_value.type = blob_type then
              set_field(p_object_handle,lower(p_new_name),get_blob_field(p_object_handle,lower(p_name)));
            end if;
       end;
                              
       procedure copy_field(p_object_handle_f pls_integer, 
                            p_object_handle_t pls_integer, 
                            p_field_name      varchar2)
       as
          v_value             value_pointer;
       begin
            v_value := get_value_pointer(p_object_handle_f, lower(p_field_name));

            if v_value.type = number_type then
              set_field(p_object_handle_t,lower(p_field_name),get_number_field(p_object_handle_f,lower(p_field_name)));
            elsif v_value.type = date_type then
              set_field(p_object_handle_t,lower(p_field_name),get_date_field(p_object_handle_f,lower(p_field_name)));
            elsif v_value.type = varchar2_type then
              set_field(p_object_handle_t,lower(p_field_name),get_varchar2_field(p_object_handle_f,lower(p_field_name)));
            elsif v_value.type = clob_type then
              set_field(p_object_handle_t,lower(p_field_name),get_clob_field(p_object_handle_f,lower(p_field_name)));
            elsif v_value.type = blob_type then
              set_field(p_object_handle_t,lower(p_field_name),get_blob_field(p_object_handle_f,lower(p_field_name)));
            end if;
       end;
       
       function get_date_field(p_object_handle pls_integer,
                               p_field_name    varchar2)
       return date
       as
           v_object        value_map:= get_object(p_object_handle);     
           v_value         value_pointer;
       begin
           v_value:= v_object(lower(p_field_name));
           return get_date_value(v_value.handle, v_value.type);
       exception
        when no_data_found then
          return null;
       end;

       function get_number_field(p_object_handle pls_integer,
                                 p_field_name    varchar2)
       return number
       as
           v_object        value_map:= get_object(p_object_handle);     
           v_value         value_pointer;
       begin
           v_value:= v_object(lower(p_field_name));
           return get_number_value(v_value.handle, v_value.type);
       exception
        when no_data_found then
           return null;
       end;

       function get_varchar2_field(p_object_handle pls_integer,
                                   p_field_name    varchar2)
       return varchar2
       as
           v_object        value_map:= get_object(p_object_handle);     
           v_value         value_pointer;
       begin
           v_value:= v_object(lower(p_field_name));
           return get_varchar2_value(v_value.handle, v_value.type);
       exception
        when no_data_found then
          return null;
       end;

       function get_clob_field(p_object_handle pls_integer,
                               p_field_name    varchar2)
       return clob
       as
           v_object        value_map:= get_object(p_object_handle);     
           v_value         value_pointer;
       begin
           v_value:= v_object(lower(p_field_name));
           return get_clob_value(v_value.handle, v_value.type);
       exception
        when no_data_found then
           return null;
       end;

       function get_blob_field(p_object_handle pls_integer,
                               p_field_name    varchar2)
       return blob
       as
           v_object        value_map:= get_object(p_object_handle);     
           v_value         value_pointer;
       begin
           v_value:= v_object(lower(p_field_name));
           return get_blob_value(v_value.handle, v_value.type);
       exception
        when no_data_found then
           return null;
       end;

       procedure nextval (p_object_handle in number, p_seq in varchar2, p_field_name in varchar2)
       as
         v_block varchar2(32000) :=    
           'declare
              cursor cur is 
              select '||p_seq||'.nextval from dual;
              rec cur%rowtype;
            begin
              open cur;
              fetch cur into :retval;
              close cur;
            end;';
         v_n number;
       begin
         execute immediate v_block using  out v_n;
         set_field(p_object_handle, lower(p_field_name), v_n);
       end;
       
       function eval (p_object_handle in number, p_str in varchar2)
       return varchar2
       as
         l_arr vc_arr;
         l_str varchar2(32767) := '';
         l_value_pointer value_pointer;

         function eval_field(p_name varchar2)
         return varchar2
         as
           l_parts    vc_arr:= split(p_name,'.');
           v_handle   number:= p_object_handle;
         begin

           if l_parts.count = 1 then
             return get_varchar2_field(p_object_handle,p_name);
           else

             for i in 1..l_parts.count-1 loop
                v_handle:= get_number_field(v_handle, l_parts(i));
             end loop;

             dbms_output.put_line('last: '||l_parts(l_parts.count));

             return get_varchar2_field(v_handle,l_parts(l_parts.count));
             
           end if; 

         end;

       begin
         l_arr := split(p_str, '{');

         if l_arr.count = 1 then
           return p_str;
         end if;

         for i in 1..l_arr.count loop
              declare
                 l_arr2  vc_arr:= split(l_arr(i), '}');
              begin
                 if l_arr2.count = 1 then
                      l_str:= l_str||l_arr(i);
                 else
                 for j in 1..l_arr2.count loop
                    if mod(j,2) = 0 then
                      l_str:= l_str||l_arr2(j);
                    else
                      l_str:= l_str||eval_field(l_arr2(j));
                    end if;
                 end loop;
                 end if;
              end;
         end loop;
         
         return l_str;
       end;

end;
/

